﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class OrderSelectionDA : IOrderSelectionDA
    {
        private string ABC_END = "@ABC_END";
        private string ABC_START = "@ABC_START";
        private string BATCH_PICK_MAX_COUNT = "@BATCH_PICK_MAX_COUNT";
        private string BATCH_PICK_MAX_CUBE = "@BATCH_PICK_MAX_CUBE";
        private string CARRIER_ID_END = "@CARRIER_ID_END";
        private string CARRIER_ID_START = "@CARRIER_ID_START";
        private string CARTON_GROUP = "@CARTON_GROUP";
        private string CITY_BEGIN = "@CITY_BEGIN";
        private string CITY_END = "@CITY_END";
        private string COMPANY_END = "@COMPANY_END";
        private string COMPANY_START = "@COMPANY_START";
        private string CONSIGNEE_ID_END = "@CONSIGNEE_ID_END";
        private string CONSIGNEE_ID_START = "@CONSIGNEE_ID_START";
        private string CREATED_BY = "@CREATED_BY";
        private string DELIVERY_DATE_END = "@DELIVERY_DATE_END";
        private string DELIVERY_DATE_START = "@DELIVERY_DATE_START";
        private string DESCR = "@DESCR";
        private string DOOR_END = "@DOOR_END";
        private string DOOR_START = "@DOOR_START";
        private string ENABLE_PACKING = "@ENABLE_PACKING";
        private string EXTERN_ORDER_ID_END = "@EXTERN_ORDER_ID_END";
        private string EXTERN_ORDER_ID_START = "@EXTERN_ORDER_ID_START";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string IS_DEFAULT = "@IS_DEFAULT";
        private string LOAD_ID_END = "@LOAD_ID_END";
        private string LOAD_ID_START = "@LOAD_ID_START";
        private string LOT_END = "@LOT_END";
        private string LOT_START = "@LOT_START";
        private string MAX_ORDERS = "@MAX_ORDERS";
        private string ORDER_DATE_END = "@ORDER_DATE_END";
        private string ORDER_DATE_START = "@ORDER_DATE_START";
        private string ORDER_GROUP_END = "@ORDER_GROUP_END";
        private string ORDER_GROUP_START = "@ORDER_GROUP_START";
        private string ORDER_HANDING_TYPE_END = "@ORDER_HANDING_TYPE_END";
        private string ORDER_HANDING_TYPE_START = "@ORDER_HANDING_TYPE_START";
        private string ORDER_ID_END = "@ORDER_ID_END";
        private string ORDER_ID_START = "@ORDER_ID_START";
        private string ORDER_PRIORITY_END = "@ORDER_PRIORITY_END";
        private string ORDER_PRIORITY_START = "@ORDER_PRIORITY_START";
        private string ORDER_SELECTION_ID = "@ORDER_SELECTION_ID";
        private string ORDER_STATUS_END = "@ORDER_STATUS_END";
        private string ORDER_STATUS_START = "@ORDER_STATUS_START";
        private string ORDER_TYPE_END = "@ORDER_TYPE_END";
        private string ORDER_TYPE_START = "@ORDER_TYPE_START";
        private string OWNER_ID_END = "@OWNER_ID_END";
        private string OWNER_ID_START = "@OWNER_ID_START";
        private string ROUTE_END = "@ROUTE_END";
        private string ROUTE_START = "@ROUTE_START";
        private string SKU_CLASS_ID_END = "@SKU_CLASS_ID_END";
        private string SKU_CLASS_ID_START = "@SKU_CLASS_ID_START";
        private string SKU_GROUP_END = "@SKU_GROUP_END";
        private string SKU_GROUP_START = "@SKU_GROUP_START";
        private string SKU_GROUP2_END = "@SKU_GROUP2_END";
        private string SKU_GROUP2_START = "@SKU_GROUP2_START";
        private string SKU_ID_END = "@SKU_ID_END";
        private string SKU_ID_START = "@SKU_ID_START";
        private string SQL_CHECK_ORDER_SELECTION_ID_UNIQUE = " SELECT COUNT(1) FROM @_@ORDER_SELECTION WHERE ORDER_SELECTION_ID = @ORDER_SELECTION_ID ";
        private string SQL_DELETE_ORDER_SELECTION = " DELETE FROM @_@ORDER_SELECTION WHERE ORDER_SELECTION_ID = @ORDER_SELECTION_ID ";
        private string SQL_INSERT_ORDER_SELECTION = " INSERT INTO @_@ORDER_SELECTION ( WH_ID, ORDER_SELECTION_ID, DESCR, IS_DEFAULT, ORDER_STATUS_START, ORDER_STATUS_END, ORDER_DATE_START, ORDER_DATE_END, DELIVERY_DATE_START, DELIVERY_DATE_END, ORDER_TYPE_START, ORDER_TYPE_END, ORDER_GROUP_START, ORDER_GROUP_END, ORDER_PRIORITY_START, ORDER_PRIORITY_END, OWNER_ID_START, OWNER_ID_END, ORDER_ID_START, ORDER_ID_END, EXTERN_ORDER_ID_START, EXTERN_ORDER_ID_END, CONSIGNEE_ID_START, CONSIGNEE_ID_END, CARRIER_ID_START, CARRIER_ID_END, ROUTE_START, ROUTE_END, DOOR_START, DOOR_END, STOP_START, STOP_END, LOAD_ID_START, LOAD_ID_END, COMPANY_START, COMPANY_END, STATE_START, STATE_END, ZIP_START, ZIP_END, CITY_BEGIN, CITY_END, CARTON_GROUP, ENABLE_PACKING, MAX_ORDERS, BATCH_PICK_MAX_CUBE, BATCH_PICK_MAX_COUNT, ORDER_HANDING_TYPE_START, ORDER_HANDING_TYPE_END, SKU_CLASS_ID_START, SKU_CLASS_ID_END, SKU_GROUP_START, SKU_GROUP_END, SKU_GROUP2_START, SKU_GROUP2_END, SKU_ID_START, SKU_ID_END, ABC_START, ABC_END, LOT_START, LOT_END, UDF1_START, UDF1_END, UDF2_START, UDF2_END, UDF3_START, UDF3_END, UDF4_START, UDF4_END, UDF5_START, UDF5_END, IS_ACTIVE, SYSTEM_CREATED, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @ORDER_SELECTION_ID, @DESCR, @IS_DEFAULT, @ORDER_STATUS_START, @ORDER_STATUS_END, @ORDER_DATE_START, @ORDER_DATE_END, @DELIVERY_DATE_START, @DELIVERY_DATE_END, @ORDER_TYPE_START, @ORDER_TYPE_END, @ORDER_GROUP_START, @ORDER_GROUP_END, @ORDER_PRIORITY_START, @ORDER_PRIORITY_END, @OWNER_ID_START, @OWNER_ID_END, @ORDER_ID_START, @ORDER_ID_END, @EXTERN_ORDER_ID_START, @EXTERN_ORDER_ID_END, @CONSIGNEE_ID_START, @CONSIGNEE_ID_END, @CARRIER_ID_START, @CARRIER_ID_END, @ROUTE_START, @ROUTE_END, @DOOR_START, @DOOR_END, @STOP_START, @STOP_END, @LOAD_ID_START, @LOAD_ID_END, @COMPANY_START, @COMPANY_END, @STATE_START, @STATE_END, @ZIP_START, @ZIP_END, @CITY_BEGIN, @CITY_END, @CARTON_GROUP, @ENABLE_PACKING, @MAX_ORDERS, @BATCH_PICK_MAX_CUBE, @BATCH_PICK_MAX_COUNT, @ORDER_HANDING_TYPE_START, @ORDER_HANDING_TYPE_END, @SKU_CLASS_ID_START, @SKU_CLASS_ID_END, @SKU_GROUP_START, @SKU_GROUP_END, @SKU_GROUP2_START, @SKU_GROUP2_END, @SKU_ID_START, @SKU_ID_END, @ABC_START, @ABC_END, @LOT_START, @LOT_END, @UDF1_START, @UDF1_END, @UDF2_START, @UDF2_END, @UDF3_START, @UDF3_END, @UDF4_START, @UDF4_END, @UDF5_START, @UDF5_END, @IS_ACTIVE, @SYSTEM_CREATED, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_ORDER_SELECTION = " SELECT  ROWID, WH_ID, ORDER_SELECTION_ID, DESCR, IS_DEFAULT, ORDER_STATUS_START, ORDER_STATUS_END, ORDER_DATE_START, ORDER_DATE_END, DELIVERY_DATE_START, DELIVERY_DATE_END, ORDER_TYPE_START, ORDER_TYPE_END, ORDER_GROUP_START, ORDER_GROUP_END, ORDER_PRIORITY_START, ORDER_PRIORITY_END, OWNER_ID_START, OWNER_ID_END, ORDER_ID_START, ORDER_ID_END, EXTERN_ORDER_ID_START, EXTERN_ORDER_ID_END, CONSIGNEE_ID_START, CONSIGNEE_ID_END, CARRIER_ID_START, CARRIER_ID_END, ROUTE_START, ROUTE_END, DOOR_START, DOOR_END, STOP_START, STOP_END, LOAD_ID_START, LOAD_ID_END, COMPANY_START, COMPANY_END, STATE_START, STATE_END, ZIP_START, ZIP_END, CITY_BEGIN, CITY_END, CARTON_GROUP, ENABLE_PACKING, MAX_ORDERS, BATCH_PICK_MAX_CUBE, BATCH_PICK_MAX_COUNT, ORDER_HANDING_TYPE_START, ORDER_HANDING_TYPE_END, SKU_CLASS_ID_START, SKU_CLASS_ID_END, SKU_GROUP_START, SKU_GROUP_END, SKU_GROUP2_START, SKU_GROUP2_END, SKU_ID_START, SKU_ID_END, ABC_START, ABC_END, LOT_START, LOT_END, UDF1_START, UDF1_END, UDF2_START, UDF2_END, UDF3_START, UDF3_END, UDF4_START, UDF4_END, UDF5_START, UDF5_END, IS_ACTIVE, SYSTEM_CREATED, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@ORDER_SELECTION ";
        private string SQL_UPDATE_ORDER_SELECTION = " UPDATE @_@ORDER_SELECTION SET WH_ID = @WH_ID, DESCR = @DESCR, IS_DEFAULT = @IS_DEFAULT, ORDER_STATUS_START = @ORDER_STATUS_START, ORDER_STATUS_END = @ORDER_STATUS_END, ORDER_DATE_START = @ORDER_DATE_START, ORDER_DATE_END = @ORDER_DATE_END, DELIVERY_DATE_START = @DELIVERY_DATE_START, DELIVERY_DATE_END = @DELIVERY_DATE_END, ORDER_TYPE_START = @ORDER_TYPE_START, ORDER_TYPE_END = @ORDER_TYPE_END, ORDER_GROUP_START = @ORDER_GROUP_START, ORDER_GROUP_END = @ORDER_GROUP_END, ORDER_PRIORITY_START = @ORDER_PRIORITY_START, ORDER_PRIORITY_END = @ORDER_PRIORITY_END, OWNER_ID_START = @OWNER_ID_START, OWNER_ID_END = @OWNER_ID_END, ORDER_ID_START = @ORDER_ID_START, ORDER_ID_END = @ORDER_ID_END, EXTERN_ORDER_ID_START = @EXTERN_ORDER_ID_START, EXTERN_ORDER_ID_END = @EXTERN_ORDER_ID_END, CONSIGNEE_ID_START = @CONSIGNEE_ID_START, CONSIGNEE_ID_END = @CONSIGNEE_ID_END, CARRIER_ID_START = @CARRIER_ID_START, CARRIER_ID_END = @CARRIER_ID_END, ROUTE_START = @ROUTE_START, ROUTE_END = @ROUTE_END, DOOR_START = @DOOR_START, DOOR_END = @DOOR_END, STOP_START = @STOP_START, STOP_END = @STOP_END, LOAD_ID_START = @LOAD_ID_START, LOAD_ID_END = @LOAD_ID_END, COMPANY_START = @COMPANY_START, COMPANY_END = @COMPANY_END, STATE_START = @STATE_START, STATE_END = @STATE_END, ZIP_START = @ZIP_START, ZIP_END = @ZIP_END, CITY_BEGIN = @CITY_BEGIN, CITY_END = @CITY_END, CARTON_GROUP = @CARTON_GROUP, ENABLE_PACKING = @ENABLE_PACKING, MAX_ORDERS = @MAX_ORDERS, BATCH_PICK_MAX_CUBE = @BATCH_PICK_MAX_CUBE, BATCH_PICK_MAX_COUNT = @BATCH_PICK_MAX_COUNT, ORDER_HANDING_TYPE_START = @ORDER_HANDING_TYPE_START, ORDER_HANDING_TYPE_END = @ORDER_HANDING_TYPE_END, SKU_CLASS_ID_START = @SKU_CLASS_ID_START, SKU_CLASS_ID_END = @SKU_CLASS_ID_END, SKU_GROUP_START = @SKU_GROUP_START, SKU_GROUP_END = @SKU_GROUP_END, SKU_GROUP2_START = @SKU_GROUP2_START, SKU_GROUP2_END = @SKU_GROUP2_END, SKU_ID_START = @SKU_ID_START, SKU_ID_END = @SKU_ID_END, ABC_START = @ABC_START, ABC_END = @ABC_END, LOT_START = @LOT_START, LOT_END = @LOT_END, UDF1_START = @UDF1_START, UDF1_END = @UDF1_END, UDF2_START = @UDF2_START, UDF2_END = @UDF2_END, UDF3_START = @UDF3_START, UDF3_END = @UDF3_END, UDF4_START = @UDF4_START, UDF4_END = @UDF4_END, UDF5_START = @UDF5_START, UDF5_END = @UDF5_END, IS_ACTIVE = @IS_ACTIVE, SYSTEM_CREATED = @SYSTEM_CREATED, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_SELECTION_ID = @ORDER_SELECTION_ID  ";
        private string STATE_END = "@STATE_END";
        private string STATE_START = "@STATE_START";
        private string STOP_END = "@STOP_END";
        private string STOP_START = "@STOP_START";
        private string SYSTEM_CREATED = "@SYSTEM_CREATED";
        private string TS = "@TS";
        private string UDF1_END = "@UDF1_END";
        private string UDF1_START = "@UDF1_START";
        private string UDF2_END = "@UDF2_END";
        private string UDF2_START = "@UDF2_START";
        private string UDF3_END = "@UDF3_END";
        private string UDF3_START = "@UDF3_START";
        private string UDF4_END = "@UDF4_END";
        private string UDF4_START = "@UDF4_START";
        private string UDF5_END = "@UDF5_END";
        private string UDF5_START = "@UDF5_START";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";
        private string ZIP_END = "@ZIP_END";
        private string ZIP_START = "@ZIP_START";

        public bool CheckOrderSelectionIDUnique(string orderSelectionID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_SELECTION_ID", orderSelectionID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_ORDER_SELECTION_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckOrderSelectionIDUnique(DataBase dataBase, DbTransaction tran, string orderSelectionID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_SELECTION_ID", orderSelectionID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_ORDER_SELECTION_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteOrderSelection(List<string> orderSelectionID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderSelectionID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_SELECTION_ID", orderSelectionID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_SELECTION.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteOrderSelection(DataBase dataBase, DbTransaction tran, List<string> orderSelectionID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderSelectionID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_SELECTION_ID", orderSelectionID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_SELECTION.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllOrderSelection(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_SELECTION.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllOrderSelection(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_SELECTION.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public OrderSelectionInfo GetOrderSelectionByID(string orderselectionID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_SELECTION.Replace("@_@", whLoginID) + " WHERE ORDER_SELECTION_ID = @ORDER_SELECTION_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_SELECTION_ID", orderselectionID) };
            OrderSelectionInfo orderSelectionInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    orderSelectionInfo = new OrderSelectionInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_SELECTION_ID"].ToString(), reader["DESCR"].ToString(), reader["IS_DEFAULT"].ToString(), reader["ORDER_STATUS_START"].ToString(), reader["ORDER_STATUS_END"].ToString(), (reader["ORDER_DATE_START"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE_START"].ToString()) : (CS00003 = new DateTime()), (reader["ORDER_DATE_END"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE_END"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE_START"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE_START"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE_END"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE_END"].ToString()) : (CS00003 = new DateTime()), reader["ORDER_TYPE_START"].ToString(), reader["ORDER_TYPE_END"].ToString(), reader["ORDER_GROUP_START"].ToString(), reader["ORDER_GROUP_END"].ToString(), reader["ORDER_PRIORITY_START"].ToString(), reader["ORDER_PRIORITY_END"].ToString(), reader["OWNER_ID_START"].ToString(), reader["OWNER_ID_END"].ToString(), reader["ORDER_ID_START"].ToString(), reader["ORDER_ID_END"].ToString(), reader["EXTERN_ORDER_ID_START"].ToString(), reader["EXTERN_ORDER_ID_END"].ToString(), reader["CONSIGNEE_ID_START"].ToString(), reader["CONSIGNEE_ID_END"].ToString(), reader["CARRIER_ID_START"].ToString(), reader["CARRIER_ID_END"].ToString(), reader["ROUTE_START"].ToString(), reader["ROUTE_END"].ToString(), reader["DOOR_START"].ToString(), reader["DOOR_END"].ToString(), reader["STOP_START"].ToString(), reader["STOP_END"].ToString(), reader["LOAD_ID_START"].ToString(), reader["LOAD_ID_END"].ToString(), reader["COMPANY_START"].ToString(), reader["COMPANY_END"].ToString(), reader["STATE_START"].ToString(), reader["STATE_END"].ToString(), reader["ZIP_START"].ToString(), reader["ZIP_END"].ToString(), reader["CITY_BEGIN"].ToString(), reader["CITY_END"].ToString(), reader["CARTON_GROUP"].ToString(), reader["ENABLE_PACKING"].ToString(), (reader["MAX_ORDERS"].ToString() != "") ? int.Parse(reader["MAX_ORDERS"].ToString()) : 0, (reader["BATCH_PICK_MAX_CUBE"].ToString() != "") ? decimal.Parse(reader["BATCH_PICK_MAX_CUBE"].ToString()) : 0M, (reader["BATCH_PICK_MAX_COUNT"].ToString() != "") ? decimal.Parse(reader["BATCH_PICK_MAX_COUNT"].ToString()) : 0M, reader["ORDER_HANDING_TYPE_START"].ToString(), reader["ORDER_HANDING_TYPE_END"].ToString(), reader["SKU_CLASS_ID_START"].ToString(), reader["SKU_CLASS_ID_END"].ToString(), reader["SKU_GROUP_START"].ToString(), reader["SKU_GROUP_END"].ToString(), reader["SKU_GROUP2_START"].ToString(), reader["SKU_GROUP2_END"].ToString(), reader["SKU_ID_START"].ToString(), reader["SKU_ID_END"].ToString(), reader["ABC_START"].ToString(), reader["ABC_END"].ToString(), reader["LOT_START"].ToString(), reader["LOT_END"].ToString(), reader["UDF1_START"].ToString(), reader["UDF1_END"].ToString(), reader["UDF2_START"].ToString(), reader["UDF2_END"].ToString(), reader["UDF3_START"].ToString(), reader["UDF3_END"].ToString(), reader["UDF4_START"].ToString(), reader["UDF4_END"].ToString(), reader["UDF5_START"].ToString(), reader["UDF5_END"].ToString(), reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return orderSelectionInfo;
        }

        public OrderSelectionInfo GetOrderSelectionByID(DataBase dataBase, DbTransaction tran, string orderselectionID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_SELECTION.Replace("@_@", whLoginID) + " WHERE ORDER_SELECTION_ID = @ORDER_SELECTION_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_SELECTION_ID", orderselectionID) };
            OrderSelectionInfo orderSelectionInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                orderSelectionInfo = new OrderSelectionInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_SELECTION_ID"].ToString(), reader["DESCR"].ToString(), reader["IS_DEFAULT"].ToString(), reader["ORDER_STATUS_START"].ToString(), reader["ORDER_STATUS_END"].ToString(), (reader["ORDER_DATE_START"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE_START"].ToString()) : (CS00003 = new DateTime()), (reader["ORDER_DATE_END"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE_END"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE_START"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE_START"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE_END"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE_END"].ToString()) : (CS00003 = new DateTime()), reader["ORDER_TYPE_START"].ToString(), reader["ORDER_TYPE_END"].ToString(), reader["ORDER_GROUP_START"].ToString(), reader["ORDER_GROUP_END"].ToString(), reader["ORDER_PRIORITY_START"].ToString(), reader["ORDER_PRIORITY_END"].ToString(), reader["OWNER_ID_START"].ToString(), reader["OWNER_ID_END"].ToString(), reader["ORDER_ID_START"].ToString(), reader["ORDER_ID_END"].ToString(), reader["EXTERN_ORDER_ID_START"].ToString(), reader["EXTERN_ORDER_ID_END"].ToString(), reader["CONSIGNEE_ID_START"].ToString(), reader["CONSIGNEE_ID_END"].ToString(), reader["CARRIER_ID_START"].ToString(), reader["CARRIER_ID_END"].ToString(), reader["ROUTE_START"].ToString(), reader["ROUTE_END"].ToString(), reader["DOOR_START"].ToString(), reader["DOOR_END"].ToString(), reader["STOP_START"].ToString(), reader["STOP_END"].ToString(), reader["LOAD_ID_START"].ToString(), reader["LOAD_ID_END"].ToString(), reader["COMPANY_START"].ToString(), reader["COMPANY_END"].ToString(), reader["STATE_START"].ToString(), reader["STATE_END"].ToString(), reader["ZIP_START"].ToString(), reader["ZIP_END"].ToString(), reader["CITY_BEGIN"].ToString(), reader["CITY_END"].ToString(), reader["CARTON_GROUP"].ToString(), reader["ENABLE_PACKING"].ToString(), (reader["MAX_ORDERS"].ToString() != "") ? int.Parse(reader["MAX_ORDERS"].ToString()) : 0, (reader["BATCH_PICK_MAX_CUBE"].ToString() != "") ? decimal.Parse(reader["BATCH_PICK_MAX_CUBE"].ToString()) : 0M, (reader["BATCH_PICK_MAX_COUNT"].ToString() != "") ? decimal.Parse(reader["BATCH_PICK_MAX_COUNT"].ToString()) : 0M, reader["ORDER_HANDING_TYPE_START"].ToString(), reader["ORDER_HANDING_TYPE_END"].ToString(), reader["SKU_CLASS_ID_START"].ToString(), reader["SKU_CLASS_ID_END"].ToString(), reader["SKU_GROUP_START"].ToString(), reader["SKU_GROUP_END"].ToString(), reader["SKU_GROUP2_START"].ToString(), reader["SKU_GROUP2_END"].ToString(), reader["SKU_ID_START"].ToString(), reader["SKU_ID_END"].ToString(), reader["ABC_START"].ToString(), reader["ABC_END"].ToString(), reader["LOT_START"].ToString(), reader["LOT_END"].ToString(), reader["UDF1_START"].ToString(), reader["UDF1_END"].ToString(), reader["UDF2_START"].ToString(), reader["UDF2_END"].ToString(), reader["UDF3_START"].ToString(), reader["UDF3_END"].ToString(), reader["UDF4_START"].ToString(), reader["UDF4_END"].ToString(), reader["UDF5_START"].ToString(), reader["UDF5_END"].ToString(), reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return orderSelectionInfo;
        }

        public DataSet GetOrderSelectionByQueryList(List<string> sqlWhere, OrderSelectionQueryEntity orderselectionQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_ORDER_SELECTION.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!orderselectionQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, orderselectionQuery.CurrentPage, orderselectionQuery.PageSize, orderselectionQuery.SortField, orderselectionQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetOrderSelectionByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, OrderSelectionQueryEntity orderselectionQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_ORDER_SELECTION.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!orderselectionQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, orderselectionQuery.CurrentPage, orderselectionQuery.PageSize, orderselectionQuery.SortField, orderselectionQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertOrderSelection(OrderSelectionInfo orderSelectionInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    orderSelectionInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_OrderSelection_Parameters(orderSelectionInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ORDER_SELECTION.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertOrderSelection(DataBase dataBase, DbTransaction tran, OrderSelectionInfo orderSelectionInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    orderSelectionInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_OrderSelection_Parameters(orderSelectionInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ORDER_SELECTION.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_OrderSelection_Parameters(OrderSelectionInfo orderSelectionInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.ORDER_SELECTION_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DESCR, SqlDbType.VarChar, 100), new SqlParameter(this.IS_DEFAULT, SqlDbType.VarChar, 1), new SqlParameter(this.ORDER_STATUS_START, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_STATUS_END, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_DATE_START, SqlDbType.DateTime, 8), new SqlParameter(this.ORDER_DATE_END, SqlDbType.DateTime, 8), new SqlParameter(this.DELIVERY_DATE_START, SqlDbType.DateTime, 8), new SqlParameter(this.DELIVERY_DATE_END, SqlDbType.DateTime, 8), new SqlParameter(this.ORDER_TYPE_START, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_TYPE_END, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_GROUP_START, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_GROUP_END, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_PRIORITY_START, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_PRIORITY_END, SqlDbType.VarChar, 20), 
                new SqlParameter(this.OWNER_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERN_ORDER_ID_START, SqlDbType.VarChar, 30), new SqlParameter(this.EXTERN_ORDER_ID_END, SqlDbType.VarChar, 30), new SqlParameter(this.CONSIGNEE_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.CONSIGNEE_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.ROUTE_START, SqlDbType.VarChar, 20), new SqlParameter(this.ROUTE_END, SqlDbType.VarChar, 20), new SqlParameter(this.DOOR_START, SqlDbType.VarChar, 20), new SqlParameter(this.DOOR_END, SqlDbType.VarChar, 20), new SqlParameter(this.STOP_START, SqlDbType.VarChar, 20), new SqlParameter(this.STOP_END, SqlDbType.VarChar, 20), 
                new SqlParameter(this.LOAD_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.LOAD_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.COMPANY_START, SqlDbType.VarChar, 50), new SqlParameter(this.COMPANY_END, SqlDbType.VarChar, 50), new SqlParameter(this.STATE_START, SqlDbType.VarChar, 20), new SqlParameter(this.STATE_END, SqlDbType.VarChar, 20), new SqlParameter(this.ZIP_START, SqlDbType.VarChar, 20), new SqlParameter(this.ZIP_END, SqlDbType.VarChar, 20), new SqlParameter(this.CITY_BEGIN, SqlDbType.VarChar, 50), new SqlParameter(this.CITY_END, SqlDbType.VarChar, 50), new SqlParameter(this.CARTON_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.ENABLE_PACKING, SqlDbType.VarChar, 1), new SqlParameter(this.MAX_ORDERS, SqlDbType.Int, 4), new SqlParameter(this.BATCH_PICK_MAX_CUBE, SqlDbType.Decimal, 13), new SqlParameter(this.BATCH_PICK_MAX_COUNT, SqlDbType.Decimal, 13), new SqlParameter(this.ORDER_HANDING_TYPE_START, SqlDbType.VarChar, 20), 
                new SqlParameter(this.ORDER_HANDING_TYPE_END, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_CLASS_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_CLASS_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_GROUP_START, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_GROUP_END, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_GROUP2_START, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_GROUP2_END, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID_START, SqlDbType.VarChar, 50), new SqlParameter(this.SKU_ID_END, SqlDbType.VarChar, 50), new SqlParameter(this.ABC_START, SqlDbType.VarChar, 20), new SqlParameter(this.ABC_END, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_START, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_END, SqlDbType.VarChar, 20), new SqlParameter(this.UDF1_START, SqlDbType.VarChar, 30), new SqlParameter(this.UDF1_END, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2_START, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF2_END, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3_START, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3_END, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4_START, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4_END, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5_START, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5_END, SqlDbType.VarChar, 30), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.SYSTEM_CREATED, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(orderSelectionInfo.WhID))
            {
                paramArray[0].Value = orderSelectionInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = orderSelectionInfo.OrderSelectionID;
            paramArray[2].Value = orderSelectionInfo.Descr;
            paramArray[3].Value = orderSelectionInfo.IsDefault;
            paramArray[4].Value = orderSelectionInfo.OrderStatusStart;
            paramArray[5].Value = orderSelectionInfo.OrderStatusEnd;
            DateTime CS00003 = new DateTime();
            if (orderSelectionInfo.OrderDateStart != CS00003)
            {
                paramArray[6].Value = orderSelectionInfo.OrderDateStart;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (orderSelectionInfo.OrderDateEnd != CS00003)
            {
                paramArray[7].Value = orderSelectionInfo.OrderDateEnd;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (orderSelectionInfo.DeliveryDateStart != CS00003)
            {
                paramArray[8].Value = orderSelectionInfo.DeliveryDateStart;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (orderSelectionInfo.DeliveryDateEnd != new DateTime())
            {
                paramArray[9].Value = orderSelectionInfo.DeliveryDateEnd;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            paramArray[10].Value = orderSelectionInfo.OrderTypeStart;
            paramArray[11].Value = orderSelectionInfo.OrderTypeEnd;
            paramArray[12].Value = orderSelectionInfo.OrderGroupStart;
            paramArray[13].Value = orderSelectionInfo.OrderGroupEnd;
            paramArray[14].Value = orderSelectionInfo.OrderPriorityStart;
            paramArray[15].Value = orderSelectionInfo.OrderPriorityEnd;
            paramArray[0x10].Value = orderSelectionInfo.OwnerIdStart;
            paramArray[0x11].Value = orderSelectionInfo.OwnerIdEnd;
            paramArray[0x12].Value = orderSelectionInfo.OrderIdStart;
            paramArray[0x13].Value = orderSelectionInfo.OrderIdEnd;
            paramArray[20].Value = orderSelectionInfo.ExternOrderIdStart;
            paramArray[0x15].Value = orderSelectionInfo.ExternOrderIdEnd;
            paramArray[0x16].Value = orderSelectionInfo.ConsigneeIdStart;
            paramArray[0x17].Value = orderSelectionInfo.ConsigneeIdEnd;
            paramArray[0x18].Value = orderSelectionInfo.CarrierIdStart;
            paramArray[0x19].Value = orderSelectionInfo.CarrierIdEnd;
            paramArray[0x1a].Value = orderSelectionInfo.RouteStart;
            paramArray[0x1b].Value = orderSelectionInfo.RouteEnd;
            paramArray[0x1c].Value = orderSelectionInfo.DoorStart;
            paramArray[0x1d].Value = orderSelectionInfo.DoorEnd;
            paramArray[30].Value = orderSelectionInfo.StopStart;
            paramArray[0x1f].Value = orderSelectionInfo.StopEnd;
            paramArray[0x20].Value = orderSelectionInfo.LoadIdStart;
            paramArray[0x21].Value = orderSelectionInfo.LoadIdEnd;
            paramArray[0x22].Value = orderSelectionInfo.CompanyStart;
            paramArray[0x23].Value = orderSelectionInfo.CompanyEnd;
            paramArray[0x24].Value = orderSelectionInfo.StateStart;
            paramArray[0x25].Value = orderSelectionInfo.StateEnd;
            paramArray[0x26].Value = orderSelectionInfo.ZipStart;
            paramArray[0x27].Value = orderSelectionInfo.ZipEnd;
            paramArray[40].Value = orderSelectionInfo.CityBegin;
            paramArray[0x29].Value = orderSelectionInfo.CityEnd;
            paramArray[0x2a].Value = orderSelectionInfo.CartonGroup;
            paramArray[0x2b].Value = orderSelectionInfo.EnablePacking;
            paramArray[0x2c].Value = orderSelectionInfo.MaxOrders;
            paramArray[0x2d].Value = orderSelectionInfo.BatchPickMaxCube;
            paramArray[0x2e].Value = orderSelectionInfo.BatchPickMaxCount;
            paramArray[0x2f].Value = orderSelectionInfo.OrderHandingTypeStart;
            paramArray[0x30].Value = orderSelectionInfo.OrderHandingTypeEnd;
            paramArray[0x31].Value = orderSelectionInfo.SkuClassIdStart;
            paramArray[50].Value = orderSelectionInfo.SkuClassIdEnd;
            paramArray[0x33].Value = orderSelectionInfo.SkuGroupStart;
            paramArray[0x34].Value = orderSelectionInfo.SkuGroupEnd;
            paramArray[0x35].Value = orderSelectionInfo.SkuGroup2Start;
            paramArray[0x36].Value = orderSelectionInfo.SkuGroup2End;
            paramArray[0x37].Value = orderSelectionInfo.SkuIdStart;
            paramArray[0x38].Value = orderSelectionInfo.SkuIdEnd;
            paramArray[0x39].Value = orderSelectionInfo.AbcStart;
            paramArray[0x3a].Value = orderSelectionInfo.AbcEnd;
            paramArray[0x3b].Value = orderSelectionInfo.LotStart;
            paramArray[60].Value = orderSelectionInfo.LotEnd;
            paramArray[0x3d].Value = orderSelectionInfo.Udf1Start;
            paramArray[0x3e].Value = orderSelectionInfo.Udf1End;
            paramArray[0x3f].Value = orderSelectionInfo.Udf2Start;
            paramArray[0x40].Value = orderSelectionInfo.Udf2End;
            paramArray[0x41].Value = orderSelectionInfo.Udf3Start;
            paramArray[0x42].Value = orderSelectionInfo.Udf3End;
            paramArray[0x43].Value = orderSelectionInfo.Udf4Start;
            paramArray[0x44].Value = orderSelectionInfo.Udf4End;
            paramArray[0x45].Value = orderSelectionInfo.Udf5Start;
            paramArray[70].Value = orderSelectionInfo.Udf5End;
            paramArray[0x47].Value = orderSelectionInfo.IsActive;
            paramArray[0x48].Value = orderSelectionInfo.SystemCreated;
            paramArray[0x49].Value = orderSelectionInfo.CreatedBy;
            paramArray[0x4a].Value = orderSelectionInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateOrderSelection(OrderSelectionInfo orderSelectionInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    orderSelectionInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_OrderSelection_Parameters(orderSelectionInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_SELECTION.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateOrderSelection(DataBase dataBase, DbTransaction tran, OrderSelectionInfo orderSelectionInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    orderSelectionInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_OrderSelection_Parameters(orderSelectionInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_SELECTION.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

